Get data from other sources

You can copy data from other sources such as a text file, a Microsoft Excel worksheet, or a Microsoft Word document to the Clipboard and then paste the data into the spreadsheet.

1. In the application you want to copy from, select the data to copy and click Copy or, if the program doesn't have this button, press CTRL+C.

2. In the spreadsheet, select the upper-left cell of the area in which you want to paste the data.

   Data that you paste will overwrite existing data. To prevent loss of data, make sure the area you paste to is blank, or move the existing data to another location on the spreadsheet.

3. Click Paste on the toolbar.

For best results, the data in the Web page or text file that you want to import should be in a table and must be on the same Web server or computer as your spreadsheet. For example, if the Web page containing your spreadsheet is opened from a Web server, you can import data that resides on that Web server only.

Caution  Data is always imported beginning in cell A1 of the spreadsheet. Existing data will be overwritten without warning.

1. In the design window, make sure the spreadsheet is activated. For instructions, see Help for your design program.
2. Click Commands and Options on the toolbar, and then click the Import tab.
3. In the Data type list, select the type of file you want to import: XML, HTML, or CSV.
4. In the URL box, type the address or path of the Web page, text file, or XML file that you want to import, or click Browse to locate the file.
5. Click Import Now.

   If you want the spreadsheet to be updated with the latest data from the Web page, text file, or XML file each time the spreadsheet is opened in the browser, select the Refresh data from URL at run time check box.

Notes

• You cannot use this method to import images or controls.
• This command works only in the design program and cannot be run in the browser at run time.
• When you choose to import XML data, only data in the XML-Spreadsheet format is imported.

1. In the design window, make sure the spreadsheet is activated. For instructions, see Help for your design program.
2. Click Commands and Options on the toolbar, and then click the Data Source tab.

3. Do one of the following:

   Connect the Spreadsheet Component to a data source on the same Web page

   • Select a data source in the Data source list under Workbook data connection. If no data sources are available on the Web page, this option is not available.

     To connect the active sheet to an individual item in the data source specified for the workbook (such as a table, query, and so on), select Data member in workbook data source under Sheet data connection, and then select the data member in the list.

   Tip

   If you're working in a data access page and you have not yet created a spreadsheet, you can simply drag a table or other data member from the Microsoft Access field list onto the data access page, and follow the instructions in the Layout Wizard to create a spreadsheet that is connected to the table or other item you dragged onto the page.

   Connect the active sheet to a data source

   1. Under Sheet data connection, select Sheet data source.

   2. In the Connection box, type the connection string for the data source, or click Edit and follow the directions in the dialog box to connect to the data source you want to use.
   3. If necessary, type the command text or SQL string you want to use to retrieve data from the data source in the Command text or SQL box.

   Note    In step 2, if do one of the following:

   • Click Edit, and then open a Data Retrieval Services Connections (.uxdc) file from the Select Data Source dialog box.
   • Click Edit, click New Source, and then select Microsoft Business Solutions or Data retrieval services and define a new connection in the Data Connection Wizard.

   The Spreadsheet component will create a databound XML list in your spreadsheet. For more information on XML lists, see About XML lists

   To use these data retrieval services data sources, you must have access to a server that is running Windows SharePoint Services. A default installation of Windows SharePoint Services provides a data retrieval service for connecting to data in SharePoint lists. A Windows SharePoint Services site administrator can install the Microsoft Office Web Parts and Components to install additional data retrieval services for Microsoft SQL Server and Microsoft Business Solutions. The installation program for Microsoft Office Web Parts and Components is available from Downloads on Microsoft Office Online.

   Security Note    Under some circumstances, a user can view the information used to connect to an external data source. This may represent a security risk. For more information, see About making connections to external data sources more secure.